﻿Option Explicit On

Imports System.Text
Imports System.IO
Imports System.Diagnostics.Process

Public Class Documents

    Public Shared Function GetDocReqTypes() As DataTable
        Dim db As New TestData.DataAccessSoapClient
        Try


            Return db.GetSQLDataTable("Select docreqtype_id,DocReqType_Name from L_DocReqTypes Order By DocReqType_Name")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetDocsRequiredForCase(ByVal CaseId As Long) As DataTable
        Dim db As New TestData.DataAccessSoapClient
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder

            With SQL
                '.Append("SELECT DocReq_ID, DocReq_Name, DocReqType_Name, docreq_dtentered, CASE DocReq_Received WHEN 0 THEN '' ELSE 'Received' END AS Rec, DocReq_DTReceived, DocReq_RecBy")
                '.Append(" FROM D_DocReqs")
                '.Append(" Left Outer Join L_DocReqTypes on D_DocReqs.docreq_TypeID = DocReqType_ID")
                '.Append(" WHERE DocReq_CaseID = " & CaseId)
                '.Append(" ORDER BY DocReq_Name")

                .Append(" SELECT     TOP 100 PERCENT dbo.D_DocReqs.DocReq_ID, dbo.D_DocReqs.DocReq_Name, dbo.L_DocReqTypes.DocReqType_Name, ")
                .Append(" dbo.D_DocReqs.DocReq_DTEntered, CASE DocReq_Received WHEN 0 THEN '' ELSE 'Received' END AS Rec, dbo.D_DocReqs.DocReq_DTReceived, ")
                .Append(" dbo.D_DocReqs.DocReq_RecBy, dbo.L_DocComments.DocComment_Msg")
                .Append(" FROM         dbo.D_DocReqs INNER JOIN")
                .Append(" dbo.L_DocComments ON dbo.D_DocReqs.DocReq_CommentId = dbo.L_DocComments.DocComment_Id LEFT OUTER JOIN")
                .Append(" dbo.L_DocReqTypes ON dbo.D_DocReqs.DocReq_TypeID = dbo.L_DocReqTypes.DocReqType_ID")
                .Append(" WHERE     dbo.D_DocReqs.DocReq_CaseID = " & CaseId)
                .Append(" ORDER BY dbo.D_DocReqs.DocReq_Name")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetCaseDocsInStore(ByVal CaseId As Long) As DataTable
        Dim db As New TestData.DataAccessSoapClient
        Try
            Dim SQL As New StringBuilder

            With SQL
                .Append("SELECT DocStore_ID, Max(DocStore_Description) as DocStore_Description, Max(DocStore_Type) as DocStore_Type, Max(DocStore_DTEntered) AS Date_Entered, Max(DocStore_EnteredBy) as DocStore_EnteredBy, count(Doc_ID) as Doc_ID")
                .Append(" FROM D_DocStore LEFT OUTER JOIN D_Docs ON D_DocStore.DocStore_ID = D_Docs.Doc_DocStoreID")
                .Append(" WHERE DocStore_CaseID = " & CaseId)
                .Append(" GROUP BY Docstore_ID ORDER BY Date_Entered DESC")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function UpdateReceivedDate(ByVal ReceivedBy As String, ByVal DocIds As String, ByVal EventMessage As String, ByVal CaseId As Long) As Boolean
        Dim db As New TestData.DataAccessSoapClient
        Dim CommonFuncs As New Global_Visas_Business_Layer.CommonFuncs
        Dim tsk As New Global_Visas_Business_Layer.TasksEvents

        Try
            Dim SQL As New StringBuilder

            With SQL
                .Append("Update D_DocReqs set ")
                .Append(" DocReq_Received = 1, ")
                .Append(" DocReq_DTReceived = getdate(), ")
                .Append(" DocReq_RecBy = '" & CommonFuncs.SwapQuotes(ReceivedBy) & "'")
                .Append(" where DocReq_ID in " & DocIds)
            End With

            If db.ExecuteSQL(SQL.ToString) Then
                tsk.GblFunc_Add_Event("Docs received", EventMessage, TasksEvents.EventStatus.SingleEvent, CaseId, TasksEvents.EventTypes.EventCase)
                Return True
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            CommonFuncs = Nothing
            tsk = Nothing
        End Try
    End Function
    
    
    
    Public Shared Function DeleteDocRequirements(ByVal CaseId As Long) As Boolean
        Dim db As New TestData.DataAccessSoapClient
        Try
            Dim SQL As New StringBuilder

            With SQL
                .Append("Delete from D_DocReqs Where DocReq_CaseID = " & CaseId)
                .Append(" And DocReq_Received = 0")
                Return db.ExecuteSQL(SQL.ToString)
            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function DeleteDocRequirementList(ByVal CaseId As Long, ByVal DocIdList As String, ByVal DocList As String) As Boolean
        Dim db As New TestData.DataAccessSoapClient
        Dim tsk As New TasksEvents
        Try
            Dim Msg As String

            If Not db.ExecuteSQL("Delete From D_DocReqs where DocReq_Id In " & DocIdList) Then
                Exit Function
            End If

            Msg = "The following document requirement has been deleted: " & vbCrLf & vbCrLf & DocList.ToString

            tsk.GblFunc_Add_Event("Details changed", Msg.ToString, TasksEvents.EventStatus.SingleEvent, CaseId, TasksEvents.EventTypes.EventCase)

            Return True
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function AddNewDocReq(ByVal CaseId As Long, ByVal DocumentName As String, ByVal DocumentTypeId As Integer, ByVal DocumentTypeName As String, ByVal EnteredBy As String) As Long
        Dim db As New TestData.DataAccessSoapClient
        Dim cmn As New CommonFuncs
        Dim tsk As New TasksEvents
        Try
            Dim SQL As New StringBuilder

            With SQL
                .Append("Insert Into D_DocReqs (DocReq_Enteredby, DocReq_DTentered, DocReq_Name, DocReq_Type, DocReq_TypeID, DocReq_CaseID)  ")
                .Append(" Values('" & cmn.SwapQuotes(EnteredBy) & "','" & cmn.FormatDate(Now, True) & "','" & cmn.SwapQuotes(DocumentName) & "','")
                .Append(cmn.SwapQuotes(DocumentTypeName) & "'," & DocumentTypeId & " ," & CaseId & ")")
            End With

            Dim id As Long = db.ExecuteSQL1(SQL.ToString, True)

            'Dim Id As Long = db.GetIdentity
            Dim Msg As String

            Msg = "Document requirement added: " & vbCrLf & vbCrLf & DocumentTypeName & " : " & DocumentName

            tsk.GblFunc_Add_Event("Details changed", Msg, TasksEvents.EventStatus.SingleEvent, CaseId, TasksEvents.EventTypes.EventCase)

            Return Id


        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            cmn = Nothing
            tsk = Nothing
        End Try
    End Function

    Public Shared Function PrintFile(ByVal strFilePath As String) As Boolean
        ' print selected file (this should work for all file types, including .doc and .pdf)
        Dim MyProcess As New Process
        Try
            MyProcess.StartInfo.CreateNoWindow = True
            MyProcess.StartInfo.Verb = "print"
            MyProcess.StartInfo.FileName = strFilePath
            MyProcess.Start()
            'MyProcess.WaitForExit(10000)
            'MyProcess.CloseMainWindow()
            MyProcess.Close()

            PrintFile = True

        Catch ex As Exception

            MsgBox("There was a problem printing the following file: " & strFilePath & ". Please contact IT.")
            PrintFile = False

        Finally
            Try
                MyProcess.StartInfo.Verb = "exit"
            Catch ex As Exception
                ' do nothing
            Finally
                MyProcess = Nothing
            End Try
        End Try

    End Function

    Public Shared Function OpenFile(ByVal DocName As String) As Boolean
        'Opens a document in it's associated application

        Try
            Start(DocName)
            Return True
        Catch
            Return False
        End Try

    End Function

    Public Shared Function GetDocsInDocStore(ByVal intDocStoreID As Integer) As DataTable
        ' get all the documents in the specified docstore folder
        Dim db As New TestData.DataAccessSoapClient
        Try
            Dim SQL As New StringBuilder

            SQL.Append("Select Doc_ID, Doc_DocStoreID, Doc_FileName, Doc_Name From D_Docs Where Doc_DocStoreID = " & intDocStoreID)

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
        End Try
    End Function

    Public Shared Function GetDocCount(ByVal caseid As Long) As Integer
        Dim sql As StringBuilder
        Dim db As New TestData.DataAccessSoapClient
        Try
            sql = New StringBuilder
            With sql
                .Append("SELECT     COUNT(*) AS filecount")
                .Append(" FROM dbo.D_DocReqs")
                .Append(" WHERE DocReq_CaseID = " & caseid)
            End With
            Return db.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            sql = Nothing
        End Try
    End Function

    Public Shared Function CheckDefaultDocument(ByVal ProductId As Long) As Integer
        Dim db As New TestData.DataAccessSoapClient
        Try
            db.GetSQLScalar("Select DefDocReq_ID from D_DefDocReqs where DefDocReq_ProductID = " & ProductId)
        Catch ex As Exception
            Return 0
        Finally
            db = Nothing
        End Try
    End Function
    Public Shared Function GetDefaultDocument(ByVal caseid As Long) As Integer
        Dim sql As StringBuilder
        Dim db As New TestData.DataAccessSoapClient
        Try
            sql = New StringBuilder
            With sql
                .Append("Select DocReq_ID from D_DocReqs ")
                .Append(" Where DocReq_CaseID = " & caseid)
                .Append(" And DocReq_Received = 0")
            End With
            Return db.GetSQLScalar(sql.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            sql = Nothing
        End Try
    End Function


    Public Shared Function GetDefaultDocumentCount(ByVal caseid As Long, ByVal Candidateid As Long, ByVal companyid As Long) As Integer
        Dim sql As StringBuilder
        Dim db As New TestData.DataAccessSoapClient
        Try
            sql = New StringBuilder

            With sql
                .Append("Select count(*) from D_Case ")
                .Append(" Where Case_ID < " & caseid)
                .Append(" And Case_StatusID Between 100 and 1020")

                If GlobalCRM.CurrentCompanyID = 0 Then
                    .Append(" AND case_CandidateId = " & Candidateid)
                Else
                    .Append(" AND case_CompanyId = " & companyid)
                End If

            End With
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            sql = Nothing
        End Try
    End Function
    Public Shared Function DocCommentList() As DataTable
        Dim db As New TestData.DataAccessSoapClient
        Try
            Return db.GetSQLDataTable("Select DocComment_Id,DocComment_Msg from L_DocComments")
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing

        End Try
    End Function
    Public Shared Function OutstandingDocs(ByVal CaseId As Long) As DataTable
        Dim db As New TestData.DataAccessSoapClient
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            With SQL
                .Append("Select DocReq_Name, DocReq_Type ")
                .Append(" FROM  D_DocReqs ")
                .Append(" WHERE DocReq_CaseID = " & CaseId & " AND DocReq_Received = 0")
            End With

            Return db.GetSQLDataTable(SQL.ToString)
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
        End Try
    End Function
    Public Shared Function LoadDefaultDocuments(ByVal caseid As Long) As Boolean
        Dim ProductId As Long
        Dim Msg As New StringBuilder
        Dim FirstCase As Integer
        Dim db As New TestData.DataAccessSoapClient
        Dim SQL As StringBuilder
        Try

            SQL = New StringBuilder
            ProductId = Cases.GetCaseInfo(caseid).Productid
            If ProductId = 0 Then Exit Function

            If db.GetSQLScalar("Select DefDocReq_ID from D_DefDocReqs where DefDocReq_ProductID = " & ProductId) = 0 Then
                MsgBox("No default document requirements found for product " & Products.GetProductName(ProductId) & ". Please speak to an adminstrator", , "Document")
                Return False
            End If

            With SQL
                .Append("Select DocReq_ID from D_DocReqs ")
                .Append(" Where DocReq_CaseID = " & caseid)
                .Append(" And DocReq_Received = 0")
            End With

            If db.GetSQLScalar(SQL.ToString) > 0 Then

                Msg.Append("Do you want to remove the existing requirements?" & vbCrLf)
                Msg.Append("(Received requirements will not be affected.)")

                If MsgBox(Msg.ToString, vbYesNo, "Add Defaults") = MsgBoxResult.Yes Then

                    If Not Documents.DeleteDocRequirements(caseid) Then
                        Exit Function
                    End If
                End If

            End If

            Msg.Insert(0, "Default doc reqs loaded onto case" & vbCrLf)

            'Check for previous initiated cases on this account

            SQL = New StringBuilder

            With SQL
                .Append("Select count(*) from D_Case ")
                .Append(" Where Case_ID < " & caseid)
                .Append(" And Case_StatusID Between 100 and 1020")

                If GlobalCRM.CurrentCompanyID = 0 Then
                    .Append(" AND case_CandidateId = " & GlobalCRM.CurrentCandidateID)
                Else
                    .Append(" AND case_CompanyId = " & GlobalCRM.CurrentCompanyID)
                End If

            End With



            If db.GetSQLScalar(SQL.ToString) = 0 Then
                FirstCase = 1
            Else
                FirstCase = 0
            End If
        Catch ex As Exception
            Return Nothing
        Finally
            db = Nothing
            SQL = Nothing
            Msg = Nothing
        End Try
    End Function
End Class
